package jdbc;

import utils.JDBCUtilsOld;
import org.junit.Test;

import java.sql.*;

/**
 * Date:2022/2/28
 * Author:ybc
 * Description:
 */
public class TransactionTest {

    /**
     * 模拟购书功能到的事务处理
     * 1、查询图书的价格
     * 2、更新图书的库存
     * 3、更新用户的余额
     *
     * JDBC处理事务的步骤：
     * 1、关闭事务的自动提交
     * connection.setAutoCommit(false);
     * 2、有异常回滚事务，没有异常提交事务
     */

    @Test
    public void test() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456"
            );
            //关闭事务的自动提交
            connection.setAutoCommit(false);
            //查询图书的价格
            String sql1 = "select price from t_book where id = 1";
            ps = connection.prepareStatement(sql1);
            rs = ps.executeQuery();
            rs.next();
            int price = rs.getInt(1);
            //更新图书的库存
            String sql2 = "update t_book set stock = stock - 1 where id = 1";
            ps = connection.prepareStatement(sql2);
            ps.executeUpdate();
            //查询用户的余额
            String sql3 = "select balance from t_user where id = 1";
            ps = connection.prepareStatement(sql3);
            rs = ps.executeQuery();
            rs.next();
            int balance = rs.getInt(1);
            if(price > balance){
                //余额不足
                throw new RuntimeException("余额不足");
            }
            //更新用户的余额
            String sql4 = "update t_user set balance = balance - ? where id = 1";
            ps = connection.prepareStatement(sql4);
            ps.setObject(1, price);
            ps.executeUpdate();
            //表示没有异常，提交事务
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //表示有异常，回滚事务
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            JDBCUtilsOld.close(rs, ps, connection);
        }
    }

}
